#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
from pprint import _id
from encodings import iso8859_1

class DBManager:
   
    # constructor
    def __init__(self):
        self.db = MySQLdb.connect(host="localhost", user="root", passwd="", db="wikiubb")
   
    def sqlTest(self, text):
        cursor = self.db.cursor()
        
        sql = "SELECT * FROM text WHERE old_text LIKE %s"            
      
        res = ''
        try:
            cursor.execute(sql, ("%" + text + "%",))
            results = cursor.fetchall()
            for row in results:
                old_text = row[1]
                res += "<br/><br/>text=%s" % (old_text)
            
            return res
        except Exception as e:
            print "Error: unable to fecth data", e
    
    def getDocAndWordsNR(self, wordList):
        cursor = self.db.cursor()
        try:
            ids = []
            for word in wordList:
                sql = "SELECT id FROM words WHERE word = %s" % ("'" + unicode(word) + "'")
                cursor.execute(sql)
                _id = cursor.fetchone()
                if _id:
                    if (_id[0] > 0):
                        ids.append(_id[0])
            
            if len(ids) == 0:
                return None
                
            sql = "SELECT documentumID, nr, wordID FROM indexedwords WHERE"            
            for index in range(len(ids)-1):
                sql += " wordID = %s OR" % (ids[index])
            sql += " wordID = %s" % (ids[len(ids)-1])
            cursor.execute(sql)
            
            res = {}
                
            results = cursor.fetchall()   
            for row in results:
                if (row[0] not in res):  #dokID [0 0]
                    valueArray = []
                    i = 0
                    while i < len(ids):
                        valueArray.append(0)
                        i += 1
                    valueArray[ids.index(row[2])] = row[1]
                    res[row[0]] = valueArray 
                else:
                    valueArray = res[row[0]]
                    valueArray[ids.index(row[2])] = row[1]
                    res[row[0]] = valueArray
                
            return res
        except Exception as e:
            print "Error: unable to fecth data", e
    
    def getTextByID(self, docID):
        cursor = self.db.cursor()
        
        try:
            sql = "SELECT old_text FROM text WHERE old_id = %s" % docID
            cursor.execute(sql)
            docText = cursor.fetchone()
             
        except Exception as e:
            print "Error: unable to fecth data", e
        
        if docText:
            return docText[0]
        else: return ""
        
    def getTitleByDocID(self, docID):
        cursor = self.db.cursor()
        try:
            sql = "SELECT p.page_title FROM page as p, revision as r WHERE p.page_id=r.rev_page and r.rev_text_id=%s" % docID
            cursor.execute(sql)
            docText = cursor.fetchone()
             
        except Exception as e:
            print "Error: unable to fecth data", e
        
        if docText:
            return docText[0]
        else: return ""
    
    # destructor
    def __del__(self):
        self.db.close()